﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace Sbk_Manage
{
    public partial class Frm_Super : Form
    {
        public Frm_Super()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(@"D:\MaiHe\合同一览表.xls");
            Aspose.Cells.Cells cellsIns;
            for (int i = 0; i < wk.Worksheets.Count; i++)
            {
                cellsIns = wk.Worksheets[i].Cells;
                string ht_year = wk.Worksheets[i].Name.Replace("年","");
                if (cellsIns.MaxDataRow > 0)
                {
                    for (int j = 1; j < cellsIns.MaxDataRow; j++)
                    {
                        string xh = cellsIns[j, 0].StringValue.Trim();
                        string mc = cellsIns[j, 1].StringValue.Trim();
                        string gg = cellsIns[j, 2].StringValue.Trim();
                        string ghs = cellsIns[j, 3].StringValue.Trim();
                        string zj = cellsIns[j, 4].StringValue.Trim();
                        if (zj.Equals(""))
                        {
                            zj = "0";
                        }
                        string bz = cellsIns[j,6].StringValue.Trim();
                        string htpy = PinYinConverter.GetFirst(mc);
                        //查询合同编号是否已经存在
                        string htbh = "AUTO-"+ht_year + "-" + xh;
                        string htlrrq = ht_year + "-12-31";
                        if (!mc.Equals(""))
                        {
                            Boolean RetBool = Program.SqliteDB.GetSqlReader("select * from ht_info where del_flag=0 and htbh='" + htbh + "'");
                            if (!RetBool)
                            {
                                string xmbh = ht_year + "-" + xh + "-" + "000";
                                string htqdrq = ht_year + "-01-01";
                                string sqlstr = string.Format("insert into ht_info(htbh,xmbh,htmc,htpy,ghs,hte,htqdrq,htlrrq,htysrq,htlrr,del_flag,jc_flag,scan_flag)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','admin',0,0,0)", htbh, xmbh, mc, htpy, ghs, zj, htqdrq, htlrrq, htlrrq);
                                if (Program.SqliteDB.ExecuteNonQuery(sqlstr) == 1)
                                {

                                }
                            }
                            else
                            {
                                DataTable dt= Program.SqliteDB.GetDataTable("select * from ht_info where del_flag=0 and htbh='" + htbh + "'");
                                string htmc1=dt.Rows[0]["htmc"].ToString()+","+mc;
                                string htmcpy1=PinYinConverter.GetFirst(htmc1);
                                double je1=Convert.ToDouble(dt.Rows[0]["hte"].ToString())+Convert.ToDouble(zj);
                                string sqlstr = string.Format("update ht_info set htmc='{0}',htpy='{1}',hte='{2}' where del_flag=0 and htbh='{3}'", htmc1, htmcpy1, je1,htbh);
                                if (Program.SqliteDB.ExecuteNonQuery(sqlstr) == 1)
                                {

                                }

                            }
                            string sqlstrsb = string.Format("insert into sb_info(mc,sbpy,xhgg,ghsmc,memo_note,htbh,ysrq)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", mc, htpy, gg, ghs, bz, htbh, htlrrq);
                            if (Program.SqliteDB.ExecuteNonQuery(sqlstrsb) == 1)
                            {

                            }
                        }
                    }
                }
            }
        }
    }
}
